package com.huatai.datadevelop.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.huatai.datadevelop.entity.TableConfigEntity;
import com.huatai.datadevelop.entity.TableConfigLogEntity;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.monitorjbl.xlsx.StreamingReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;

/**
 * @author hecaiy
 * @ClassName syncImportService
 * @Description:TODO  线程池数据分批入库
 * @date 2023/4/25 14:58
 **/
@Service
public class SyncImportService {

    @Autowired
    private TableConfigService tableConfigService;
    @Autowired
    private TableConfigLogService tableConfigLogService;

    /**
     * 根据表名称自动装配sql
     *
     * @param tableName
     * @return
     */
    public HashMap<String, String> getTableNameToSQL(String tableName) {
        HashMap<String, String> hashMap = new HashMap<>();
        TableConfigEntity tableConfigEntity = this.tableConfigService.getOne(
                new QueryWrapper<TableConfigEntity>().eq("table_name", tableName));
        String field = tableConfigEntity.getField();
        hashMap.put("field", field);
        String sqlStr = "SELECT " + field + " FROM " + tableName + " WHERE 1=1 ";
        hashMap.put("sqlStr", sqlStr);
        return hashMap;
    }

    /**
     * 导入数据
     * @param file
     * @param tableId
     * @throws IOException
     */
    @Async("asyncServiceExecutor")
    public void importExcel(MultipartFile file, String tableId) throws IOException {
        InputStream inputStream = file.getInputStream();
        Workbook workbook = StreamingReader.builder()
                //缓存到内存中的行数，默认是10
                .rowCacheSize(100)
                //读取资源时，缓存到内存的字节大小，默认是1024
                .bufferSize(4096)
                //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
                .open(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取表名
        TableConfigEntity tableConfig = this.tableConfigService.getById(tableId);
        String tableName = tableConfig.getTableName();
        //数据头
        StringBuilder sb = new StringBuilder();
        //数据体
        StringBuilder sbValue = new StringBuilder();
        //数据体个数 50个数据体一个循环  方便入库操作
        int k = 0;
        //数据头字段
        String sbStr = "";
        //遍历所有的行
        for (Row row : sheet) {
            System.out.println("开始遍历第" + row.getRowNum() + "行数据：");
            int rowNum = row.getRowNum();
            if(rowNum>0){
                sbValue.append("(");
            }
            //遍历所有的列
            for (Cell cell : row) {
                //读取表头信息
                if(rowNum==0){
                    String stringCellValue = cell.getStringCellValue();
                    sb.append(stringCellValue).append(",");
                }else {//表体数据
                    //拼接sql所需的值域
                    String stringCellValue = cell.getStringCellValue();
                    sbValue.append("'").append(stringCellValue).append("',");
                }
            }
            //分开处理数据头
            if(rowNum==0){
                sbStr = sb.toString();
                sbStr = sbStr.substring(0, sbStr.length() - 1);
            }else {
                k++;
                sbValue.deleteCharAt(sbValue.lastIndexOf(",")).append(")").append(",");
                //50条数据入一次库
                if(k%50==0){
                    sbValue.deleteCharAt(sbValue.lastIndexOf(","));
                    String sbValueStr = sbValue.toString();
                    //数据异步入库
					this.importDataInDB(tableName, sbStr, sbValueStr);
					//入库则清空一次数据
                    sbValue.setLength(0);
                }
            }
        }

        //最后入库一次，保证所有数据入库
        if(StringUtils.isNotEmpty(sbValue.toString())){
            sbValue.deleteCharAt(sbValue.lastIndexOf(","));
            String sbValueStr = sbValue.toString();
			this.importDataInDB(tableName, sbStr, sbValueStr);
        }
    }

    /**
     * 导入数据到数据库中
     * @param tableName
     * @param sbStr
     * @param sbValueStr
     */
    @Async("asyncServiceExecutor")
    @Transactional(rollbackFor = Exception.class)
    public Boolean importDataInDB(String tableName, String sbStr, String sbValueStr) {
		Boolean aBoolean = true;
		String sqlStr=" INSERT INTO " + tableName + " (" + sbStr + ") VALUES " + sbValueStr;
        TableConfigLogEntity tableConfigLogEntity = new TableConfigLogEntity();
        tableConfigLogEntity.setTableName(tableName);
        tableConfigLogEntity.setSqlMessage(sqlStr);
        tableConfigLogEntity.setCreatTime(new Date());
        try {
			 aBoolean = this.tableConfigService.getBaseMapper().insertDataSql(sqlStr);
		}catch (Exception e){
            tableConfigLogEntity.setErrorMessage(e.getMessage());
        }finally{
            tableConfigLogService.save(tableConfigLogEntity);
        }
		return aBoolean;
    }
}
